//	CP2013 Traffic Simulator Assignment
//      Traffic Model Intersection
//	Java version
//      Coded by Matthew Johnson

package traffic;

import java.sql.*;
import org.sqlite.JDBC;

//Opens and Writes to the database
public class DatabaseAccess
{
    //The database connection
    private Connection conn;
    //Default Database
    public static final String DEFAULT_DATABASE = "results.sqlite";

    //Constructor: Opens database
    DatabaseAccess(String path) throws Exception
    {
        //Attempt to open Database
        if (!open(path))
        {
            //If database can't be opened, throw an exception
            conn = null;
            throw new Exception("Database can't be opened");
        }
    }

    //Opens the database at provided path
    public boolean open(String path)
    {
        try
        {
            //Setup the database connection
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:" + path);
            
            //Check to see if result table exist
            Statement stat = conn.createStatement();
            ResultSet res = stat.executeQuery("SELECT name FROM sqlite_master WHERE name='results'");
            //If no rows were returned, table does not exist
            boolean tableExists = false;
            while (res.next())
            {
                tableExists = true;
            }
            //If table doesn't exist, create it
            if (!tableExists)
            {
                stat.executeUpdate("CREATE TABLE results (id INTEGER PRIMARY KEY, lanes_NS INTEGER NOT NULL, lanes_WE INTEGER NOT NULL, probability_NS INTEGER NOT NULL, probability_WE INTEGER NOT NULL, greenLength INTEGER, numCycles INTEGER, carsProcessed INTEGER);");
            }
            //If we get to here the database opened successfully
            return true;
        }
        //If an exception happens, return false
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
        return false;
    }

    //Closes the database
    public void close()
    {
        if (conn != null)
        {
            try
            {
                conn.close();
                conn = null;
            }
            catch(SQLException e)
            {
                e.printStackTrace();
            }
        }
    }

    //Inserts a new record into the database
    public boolean insertResults(SimulationParams s, int numCars)
    {
        //If Connection exists
        if (conn != null)
        {
            try
            {
                    //Prepare the query
                    String query = "INSERT INTO RESULTS(";
                    query += "lanes_NS, lanes_WE, probability_NS, probability_WE, greenLength, numCycles, carsProcessed)";
                    query += "VALUES ('" + s.nsLanes + "','" + s.ewLanes + "','";
                    query += s.nsProb + "','" + s.ewProb + "','" + s.greenLength + "','";
                    query += s.numCycles + "','" + numCars + "');";

                    System.out.println(query);

                    //Execute query
                    Statement stat = conn.createStatement();
                    int rowInserted = stat.executeUpdate(query);

                    //If row was inserted then insertion was successful
                    if (rowInserted == 1)
                    {
                        return true;
                    }
            }
            catch (SQLException e)
            {
                e.printStackTrace();
                close();
            }
        }
        //We return false if no connection exists or if an exception occured
        return false;
    }
}
